package com.cefa.edu.dao;

import com.cefa.edu.dao.provider.UserProvider;
import com.cefa.edu.datasource.DataSource;
import com.cefa.edu.model.ClassInfo;
import com.cefa.edu.model.EducationLevel;
import com.cefa.edu.model.StudentInfo;
import com.cefa.edu.model.User;
import org.apache.ibatis.annotations.*;

import javax.websocket.server.PathParam;
import java.util.List;

@Mapper
public interface EducationLevelMapper {
    @DataSource("cefaedu")
    // Get SQL string from provider
    //@SelectProvider(type= UserProvider.class, method = "getUserString")
    @Results(id = "levelMap", value = {
            @Result(property = "id", column = "id"),
            @Result(property = "name", column = "name"),
            @Result(property = "level", column = "level"),
            @Result(property = "pId", column = "p_id"),
            @Result(property = "gId", column = "g_id"),
            @Result(property = "gradeDate", column = "grade_date"),


            @Result(property = "schoolId", column = "school_id"),
            @Result(property = "sort", column = "sort"),
            @Result(property = "reorder", column = "reorder"),
    })
    /**
     *
     * @param id 层级的父级id
     * @param level 层级 1、2、3
     * @return
     */
    @Select("select l.id as id, l.* from cefa_edu_user u left join cefa_edu_user_education_level ul on u.id=ul.user_id  left join \n" +
            "cefa_edu_education_level  l on l.id=ul.level_id  WHERE u.id=#{id}  and l.level=#{level} and ul.status=0 order by l.reorder asc")
    List<EducationLevel> getEducationLevel(@Param("id") Integer id, @Param("level") Integer level);


    /**
     * @param id 层级的父级id
     * @return
     */
    @Select("select id,name,level,p_id as pId,g_id as gId,grade_date as gradeDate,sort from cefa_edu_education_level WHERE p_id=#{id} order by sort desc")
    List<EducationLevel> getEducationLevelByPid(@Param("id") Integer id);
    /**
     * @param id 层级的祖级id
     * @return
     */
    @Select("select id,name,level,p_id as pId,g_id as gId,grade_date as gradeDate,sort from cefa_edu_education_level WHERE g_id=#{id} order by sort desc")
    List<EducationLevel> getEducationLevelByGid(@Param("id") Integer id);

    /**
     * @param id 权限层级id
     * @return
     */
    @Select("select id,name,level,p_id as pId,g_id as gId,grade_date as gradeDate,sort  from cefa_edu_education_level WHERE id=#{id}")
    EducationLevel getEducationLevelById(@Param("id") Integer id);

    /**
     * @param userId 用户id
     * @param level  权限层级
     * @param ids    父级id集合
     * @return
     */
    @Select("select l.id ,l.name,l.level, l.p_id as pId ,l.grade_date as gradeDate,l.g_id as gId from cefa_edu_user u left join cefa_edu_user_education_level ul on u.id=ul.user_id  left join cefa_edu_education_level  l on l.id=ul.level_id  WHERE u.id=#{id}  and l.level=#{level} and l.p_id not in (#{ids}) and ul.status=0")
    List<EducationLevel> getEducationLevelByIdNotIn(@Param("id") Integer userId, @Param("level") Integer level, @Param("ids") String ids);

    /**
     * @param userId 用户id
     * @param level  权限层级
     * @param ids    祖级（一级）id集合
     * @return
     */
    @Select("select l.id as id, l.p_id as pId ,l.grade_date as gradeDate,l.g_id as gId from cefa_edu_user u left join cefa_edu_user_education_level ul on u.id=ul.user_id  left join cefa_edu_education_level  l on l.id=ul.level_id  WHERE u.id=#{id}  and l.level=#{level} and l.g_id not in (#{ids}) and ul.status=0")
    List<EducationLevel> getEducationLevelByPidNotIn(@Param("id") Integer userId, @Param("level") Integer level, @Param("ids") String ids);

    /**
     * @param userId 用户id
     * @param level  权限层级 1、2、3
     * @param ids    祖级id集合
     * @param pIds   父级id集合
     * @return
     */
    @Select("select l.id as id, l.p_id as pId ,l.grade_date as gradeDate,l.g_id as gId from cefa_edu_user u left join cefa_edu_user_education_level ul on u.id=ul.user_id  left join cefa_edu_education_level  l on l.id=ul.level_id  WHERE u.id=#{id}  and l.level=#{level} and l.g_id not in (#{ids}) and l.p_id not in(#{pIds}) and ul.status=0")
    List<EducationLevel> getEducationLevelByIdAndPidNotIn(@Param("id") Integer userId, @Param("level") Integer level, @Param("ids") String ids, @Param("pIds") String pIds);

//    @Select("select l.id as id, l.p_id as pId ,l.grade_date as gradeDate,l.g_id as gId from cefa_edu_user u left join cefa_edu_user_education_level ul on u.id=ul.user_id  left join cefa_edu_education_level  l on l.id=ul.level_id  WHERE u.id=#{id}  and l.level=#{level} and l.g_id not in (#{ids}) and l.p_id not in(#{pIds})")
//    List<User> getUserList(@Param("id") Integer userId,@Param("level") Integer level,@Param("ids") String ids,@Param("pIds") String pIds);


    /**
     * @param educationLevel
     * @return
     */
    @Insert({"insert into cefa_edu_education_level(name,level,p_id,g_id,grade_date,school_id,sort,reorder) values(#{name},#{level}, #{pId}, #{gId}, #{gradeDate},#{schoolId},#{sort},#{reorder})"})
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertEducationLevel(EducationLevel educationLevel);


    @Update({"update cefa_edu_education_level set name = #{name} where id = #{id}"})
    int updateLevelName(@Param("id") Integer userId, @Param("name") String name);


    @Delete("DELETE FROM cefa_edu_user_education_level   WHERE user_id = #{userId} and level_id=#{levelId}")
    void deleteUserEducationLevelByUserId(@Param("userId") Integer userId,@Param("levelId") Integer levelId);


    @Select("select count(1) from cefa_edu_user_education_level WHERE user_id=#{userId} and level_id=#{levelId} and status=0")
    int getUserLevel(@Param("userId") Integer userId, @Param("levelId") Integer levelId);

    @Delete("DELETE FROM cefa_edu_instructor WHERE id = #{id}")
    void deleteInstructor(@Param("id") Integer id);

    @Delete("DELETE FROM cefa_edu_education_level WHERE id = #{id}")
    void deleteDucationLevel(@Param("id") Integer id);

    @Delete("DELETE FROM cefa_edu_user_education_level WHERE level_id = #{levelId}")
    void deleteUserEducationLevelByLevelId(@Param("levelId") Integer levelId);

    /**
     * 学生升级时，班级自动和上一个年级关联
     *
     * @param levelId
     * @param pId
     * @return
     */
    @Update({"update cefa_edu_education_level set p_id = ${pId} where id = #{levelId}"})
    int updateLevelPid(@Param("levelId") Integer levelId, @Param("pId") Integer pId);

    /**
     * 根据父级id和名称查询层级数据
     *
     * @param pId
     * @param name
     * @return
     */
    @Select("select * from cefa_edu_education_level where p_id=#{pId} and name=#{name}")
    List<EducationLevel> getEducationLevelBypIdAndName(@Param("pId") Integer pId, @Param("name") String name);

    @Update({"update cefa_edu_education_level set p_id =#{pId},g_id=#{gId} where id = #{levelId}"})
    int updateLevelpIdgIdIsnull(@Param("levelId") Integer levelId, @Param("pId") Integer pId, @Param("gId") Integer gId);

    @Select("select a.id,a.name,a.level,a.p_id,a.g_id,b.name as p_name,c.name as g_name from \n" +
            "(select * from cefa_edu_education_level where name is not null and \n" +
            "#{p_id} in (select id from cefa_edu_education_level where level=#{level})) a\n" +
            "left join cefa_edu_education_level b on a.p_id=b.id  \n" +
            "left join cefa_edu_education_level c on a.g_id= c.id")
    List<ClassInfo> findByLevel12(@PathParam("p_id") String p_id, @PathParam("level") Integer level);


    @Select("select a.id,a.name,a.level,a.p_id,a.g_id,b.name as p_name,c.name as g_name from \n" +
            "(select * from cefa_edu_education_level where id in(\n" +
            "select level_id from cefa_edu_user_education_level where user_id=#{user_id})) a\n" +
            "left join cefa_edu_education_level b on a.p_id=b.id  \n" +
            "left join cefa_edu_education_level c on a.g_id= c.id")
    List<ClassInfo> findByLevel34(@PathParam("user_id") Integer level);

    /**
     * 根据用户id和父级id查询层级信息
     * @param userId
     * @param pId
     * @return
     */
    @Select("select l.id ,l.name,l.level, l.p_id as pId ,l.grade_date as gradeDate,l.g_id as gId from cefa_edu_user_education_level ul left join cefa_edu_education_level l on ul.level_id=l.id where ul.user_id=#{userId} and l.p_id=#{pId} and ul.status=0")
    List<EducationLevel> getEducationLevelByuserIdAndpId(@Param("id") Integer userId, @Param("pId") Integer pId);

    @Update({ "update cefa_edu_user_education_level set status = #{status} where level_id=#{levelId}" })
    int updateUserLevelByStatus(@Param("status") Integer status,@Param("levelId") Integer levelId);


    /**
     * 根据层级和名称，查询是否该层级存在
     * @param level
     * @param name
     * @return
     */
    @Select("select count(1) from cefa_edu_education_level WHERE level=#{level} and name=#{name}")
    int getEducationLevelByLevelAndName(@Param("level") Integer level,@Param("name") String name);

    @Select("select ul.level_id as id from cefa_edu_user_education_level ul  left join cefa_edu_user u on u.id=ul.user_id where u.id=#{userId} and ul.status=0")
    @ResultMap(value="levelMap")
    List<EducationLevel> getEducationLevelByUserId(@Param("userId") Integer userId);

    @Update({ "update cefa_edu_education_level set grade_date =grade_date+1 where school_id=#{schoolId}" })
    int updateLevelByschoolId(@Param("schoolId") Integer schoolId);
}
